import itertools
import logging
import os
import re
import shutil
import time
import urllib.parse

import openpyxl

from app.config import settings


def sanitize_for_excel(text: str) -> str:
    if not isinstance(text, str):
        return text
    return re.sub(r"[\x00-\x08\x0B\x0C\x0E-\x1F]", "", text)


def load_excel_to_db(conn, file_path: str, tmp_path: str, language: str):
    filename = os.path.basename(file_path)
    logging.info(f"--- [Stage 1] Load file: {filename} ---")
    cursor = conn.cursor()
    file_id = None
    try:
        cursor.execute(
            "INSERT INTO source_files (filename, status, tmp_path, result_filename, total_rows, language) VALUES (%s, %s, %s, %s, %s, %s)",
            (filename, "PROCESSING", tmp_path, "", 0, language),
        )
        file_id = cursor.lastrowid
        conn.commit()
        logging.info(f"File '{filename}' registered in database, ID: {file_id}.")
    except Exception as e:
        conn.rollback()
        cursor.execute("SELECT id FROM source_files WHERE filename = %s", (filename,))
        result = cursor.fetchone()
        if result:
            file_id = result[0]
            logging.info(
                f"File '{filename}' already exists, ID: {file_id}. Resetting content for reload."
            )
            cursor.execute("DELETE FROM file_contents WHERE file_id = %s", (file_id,))
            cursor.execute(
                "UPDATE source_files SET status = 'PROCESSING', tmp_path = %s, total_rows = 0, language = %s WHERE id = %s",
                (tmp_path, language, file_id),
            )
            conn.commit()
        else:
            cursor.close()
            raise Exception(f"Failed to register file '{filename}' in database: {e}")
    try:
        workbook = openpyxl.load_workbook(file_path, data_only=True)
    except Exception as e:
        cursor.close()
        raise Exception(f"Unable to open workbook '{filename}': {e}")
    total_rows_found = 0
    for sheet_name in workbook.sheetnames:
        worksheet = workbook[sheet_name]
        header_row_num, header_values = -1, []
        for row_idx in range(1, min(worksheet.max_row, settings.header_scan_limit) + 1):
            current_row_values = [
                str(cell.value).strip() if cell.value else ""
                for cell in worksheet[row_idx]
            ]
            if any(alias in current_row_values for alias in settings.desc_col_aliases):
                header_row_num, header_values = row_idx, current_row_values
                break
        if header_row_num == -1:
            continue
        try:
            desc_col_idx = next(
                i for i, h in enumerate(header_values) if h in settings.desc_col_aliases
            )
        except StopIteration:
            continue

        # 尝试找到 support 列的索引
        support_col_idx = -1
        try:
            support_col_idx = next(
                i
                for i, h in enumerate(header_values)
                if h in settings.support_col_aliases
            )
        except StopIteration:
            # 如果找不到 support 列，继续处理，但不会设置 support_status
            pass

        rows_to_insert = []
        for row_idx, row in enumerate(
                worksheet.iter_rows(min_row=header_row_num + 1, values_only=True)
        ):
            description = row[desc_col_idx]
            if description and str(description).strip():
                status = "PENDING"
                support_status = ""

                # 如果找到了 support 列，并且值为 "Yes" 或 "No"，则将 status 设置为 NOT_EXECUTE
                if support_col_idx != -1 and row[support_col_idx]:
                    support_value = str(row[support_col_idx]).strip()
                    if support_value.lower() in ["yes", "no", "skip"]:
                        status = "NOT_EXECUTE"
                        support_status = support_value

                insert_data = (
                    file_id,
                    sheet_name,
                    header_row_num + 1 + row_idx,
                    str(description),
                    status,
                    support_status,
                )

                rows_to_insert.append(insert_data)
        if rows_to_insert:
            total_rows_found += len(rows_to_insert)
            try:
                cursor.executemany(
                    "INSERT INTO file_contents (file_id, worksheet_name, original_row_number, description, status, support_status) VALUES (%s, %s, %s, %s, %s, %s)",
                    rows_to_insert,
                )
                conn.commit()
                logging.info(
                    f"Loaded {cursor.rowcount} rows from worksheet '{sheet_name}' into database."
                )
            except Exception as err:
                logging.error(
                    f"Error occurred when inserting data into file_contents: {err}"
                )
                conn.rollback()
                raise Exception(f"Failed to insert data into file_contents: {err}")
    cursor.close()
    # Update total_rows in source_files after counting
    update_total_rows_cursor = conn.cursor()
    try:
        update_total_rows_cursor.execute(
            "UPDATE source_files SET total_rows=%s WHERE id=%s",
            (total_rows_found, file_id),
        )
        conn.commit()
        update_total_rows_cursor.close()
        return file_id
    except Exception as e:
        conn.rollback()
        update_total_rows_cursor.close()
        raise Exception(f"Failed to update total rows in source_files: {e}")


def export_processed_file_to_excel(conn, file_id: int):
    """
    只需 file_id 和 conn，其他信息从数据库获取。
    """
    logging.info(f"--- [Stage 4] Export data to excel, file_id: {file_id} ---")
    # 查询 source_files 获取源文件名和 tmp_path
    cursor = conn.cursor(dictionary=True)
    cursor.execute(
        "SELECT filename, tmp_path FROM source_files WHERE id = %s", (file_id,)
    )
    file_info = cursor.fetchone()
    if not file_info:
        logging.error(f"File information for file_id={file_id} not found.")
        cursor.close()
        return
    source_filename = file_info["filename"]
    tmp_path = file_info["tmp_path"]
    cursor.close()
    # 写入文件
    try:
        source_path = os.path.join(tmp_path, source_filename)
        base_name = re.sub(r"^\d{13}_", "", source_filename.rsplit(".", 1)[0])
        result_filename = f"{base_name}_result_{time.strftime("%Y%m%d_%H%M%S")}.xlsx"
        destination_path = os.path.join(tmp_path, result_filename)
        shutil.copy2(source_path, destination_path)
        logging.info(f"Copied '{source_filename}' to '{destination_path}'")
        update_result_cursor = conn.cursor()
        update_result_cursor.execute(
            "UPDATE source_files SET result_filename=%s WHERE id=%s",
            (result_filename, file_id),
        )
        conn.commit()
        update_result_cursor.close()
    except Exception as e:
        logging.error(f"Error occurred when copying file '{source_filename}': {e}")
        return
    cursor = conn.cursor(dictionary=True)
    cursor.execute(
        "SELECT * FROM file_contents WHERE file_id = %s AND status IN ('COMPLETED', 'FAILED')",
        (file_id,),
    )
    all_rows = cursor.fetchall()
    cursor.close()
    if not all_rows:
        logging.warning("No processed data found in database for this file to export.")
        return
    all_rows_by_sheet = {
        k: list(v)
        for k, v in itertools.groupby(all_rows, key=lambda r: r["worksheet_name"])
    }
    try:
        workbook = openpyxl.load_workbook(destination_path)
    except Exception as e:
        logging.error(f"load excel '{destination_path}' fail: {e}")
        return
    for sheet_name in workbook.sheetnames:
        if sheet_name not in all_rows_by_sheet:
            continue
        worksheet = workbook[sheet_name]
        logging.info(f"Updating worksheet: '{sheet_name}'")
        header_row_num, header_values = -1, []
        for row_idx in range(
                1, min(worksheet.max_row + 1, settings.header_scan_limit + 1)
        ):
            raw_row_values = [
                str(cell.value).strip() if cell.value else ""
                for cell in worksheet[row_idx]
            ]
            # 从列表末尾移除所有连续的空字符串。
            # 这可以防止因 Excel 中存在不可见的“幽灵”单元格而错误地判断表格宽度。
            while raw_row_values and not raw_row_values[-1]:
                raw_row_values.pop()

            current_row_values = raw_row_values

            if any(alias in current_row_values for alias in settings.desc_col_aliases):
                header_row_num, header_values = row_idx, current_row_values
                logging.info(f"在工作表 '{sheet_name}' 的第 {header_row_num} 行找到表头。")
                break
        if header_row_num == -1:
            logging.warning(
                f"Header not found in worksheet '{sheet_name}', cannot write results."
            )
            continue

        # def find_col(ws, header_idx, current_headers, aliases):
        #     for alias in aliases:
        #         try:
        #             return current_headers.index(alias) + 1
        #         except ValueError:
        #             continue
        #     header_idx = header_idx + 1
        #     current_headers = [str(cell.value).strip() if cell.value else "" for cell in ws[header_idx]]
        #     for alias in aliases:
        #         try:
        #             return current_headers.index(alias) + 1
        #         except ValueError:
        #             continue
        #     raise ValueError(f"Alias not found {aliases[0]} in worksheet '{sheet_name}'")

        def find_or_create_col(ws, header_idx, current_headers, aliases):
            for alias in aliases:
                try:
                    return current_headers.index(alias) + 1
                except ValueError:
                    continue
            header_idx = header_idx + 1
            current_headers = [str(cell.value).strip() if cell.value else "" for cell in ws[header_idx]]
            for alias in aliases:
                try:
                    return current_headers.index(alias) + 1
                except ValueError:
                    continue
            new_col_name = aliases[0]
            new_col_idx = len(current_headers) + 1
            ws.cell(row=header_idx, column=new_col_idx, value=new_col_name)
            current_headers.append(new_col_name)
            logging.info(
                f"Created new column '{new_col_name}' in worksheet '{sheet_name}'."
            )
            return new_col_idx

        support_col_idx = find_or_create_col(
            worksheet, header_row_num, header_values, settings.support_col_aliases
        )
        confidence_col_idx = find_or_create_col(
            worksheet, header_row_num, header_values, settings.confidence_col_aliases
        )
        source_col_idx = find_or_create_col(
            worksheet, header_row_num, header_values, settings.source_col_aliases
        )
        summary_col_idx = find_or_create_col(
            worksheet, header_row_num, header_values, settings.summary_col_aliases
        )
        rows_for_this_sheet = all_rows_by_sheet[sheet_name]
        for row_data in rows_for_this_sheet:
            excel_row_num = row_data["original_row_number"]
            try:
                worksheet.cell(row=excel_row_num, column=support_col_idx).value = (
                    sanitize_for_excel(row_data["support_status"])
                )
                worksheet.cell(row=excel_row_num, column=confidence_col_idx).value = (
                    row_data["confidence"]
                )
                worksheet.cell(row=excel_row_num, column=source_col_idx).value = (
                    sanitize_for_excel(row_data["source_documents"])
                )
                worksheet.cell(row=excel_row_num, column=summary_col_idx).value = (
                    sanitize_for_excel(row_data["summary"])
                )
            except IndexError:
                logging.warning(
                    f"Attempted row number {excel_row_num} is out of range for worksheet '{sheet_name}', skipped."
                )
    try:
        workbook.save(destination_path)
        nginx_dir = settings.nginx_dir
        nginx_prefix = settings.nginx_prefix
        dest_dir = os.path.join(nginx_dir, os.path.basename(tmp_path))
        shutil.copytree(tmp_path, dest_dir, dirs_exist_ok=True)
        logging.info(f"Copied '{tmp_path}' to nginx_dir '{dest_dir}'")
        encode_file_name = urllib.parse.quote(result_filename)
        result_file_url = (
            f"{nginx_prefix.rstrip('/')}/{os.path.basename(tmp_path)}/{encode_file_name}"
        )
        update_url_cursor = conn.cursor()
        update_url_cursor.execute(
            "UPDATE source_files SET result_file_url=%s, status='COMPLETED' WHERE id=%s",
            (result_file_url, file_id),
        )
        conn.commit()
        update_url_cursor.close()
        logging.info(
            f"✅ Successfully updated and saved results to '{os.path.basename(destination_path)}'"
        )
    except Exception as e:
        logging.error(f"Failed to save Excel file '{destination_path}': {e}")
